#!/bin/env python

import pdb
try:
    import sqlite3
except:
    from pysqlite2 import dbapi2 as sqlite3
import sys
import time
import unittest
import uuid

my_openid = 'https://esg.ccs.ornl.gov/esgf-idp/openid/tbarron4'

# ---------------------------------------------------------------------------
def main(args):
    pdb.set_trace()
    db = rqdb('test')

    reqid = uuid.uuid4()
    # insert with invalid field should not be allowed
    rc = db.insert(rqid = reqid.hex,
                   status = 'start',
                   openid = 'https://esg.ccs.ornl.gov/esgf-idp/openid/tbarron4',
                   email_addr = 'tbarron@ornl.gov')

    # insert with field 'updated' should not be allowed
    rc = db.insert(rqid = reqid.hex,
                   status = 'start',
                   updated = time.time(),
                   openid = 'https://esg.ccs.ornl.gov/esgf-idp/openid/tbarron4',
                   email_addr = 'tbarron@ornl.gov')

    # insert with request_id should not be allowed
    rc = db.insert(rqid = reqid.hex,
                   status = 'start',
                   openid = 'https://esg.ccs.ornl.gov/esgf-idp/openid/tbarron4',
                   email_addr = 'tbarron@ornl.gov')

    # insert without request_id is allowed, should return the req id
    good = db.insert(status = 'start',
                   openid = 'https://esg.ccs.ornl.gov/esgf-idp/openid/tbarron4',
                   email_addr = 'tbarron@ornl.gov')

    # update without a request_id should fail
    rc = db.update(status = 'transfer',
                   ep_src1 = 'endpoint one',
                   ep_dst = 'over there',
                   is_gc = False)
    
    # update with a request_id should fail if the request id is not in db
    rc = db.update(request_id = reqid,
                   status = 'tranfer',
                   ep_src1 = 'endpoint one',
                   ep_dst = 'over there',
                   is_gc = False)
    
    # update with a request_id should work if the request id is in db
    rc = db.update(request_id = good,
                   status = 'tranfer',
                   ep_src1 = 'endpoint one',
                   ep_dst = 'over there',
                   is_gc = False)
    
# ---------------------------------------------------------------------------
class rqdb():
    # -----------------------------------------------------------------------
    def __init__(self, *args):
        if 'test' in args:
            self.cxn = sqlite3.connect('/tmp/rqdb_test_db')
        else:
            self.cxn = sqlite3.connect('/tmp/pubsvc_request_db')
        c = self.cxn.cursor()
        try:
            c.execute('select * from request_table')
        except sqlite3.OperationalError, e:
            if 'no such table' in str(e):
                self.create_table()

    # -----------------------------------------------------------------------
    def create_table(self):
        c = self.cxn.cursor()
        table_description = """create table request_table
                                   (request_id        text,
                                    status            text,
                                    updated           integer,
                                    ep_src1           text,
                                    ep_src2           text,
                                    ep_dst            text,
                                    is_gc             integer,
                                    openid            text,
                                    email_addr        text,
                                    xfr1              text,
                                    xfr2              text)
                                    """
        c.execute(table_description)

    # -----------------------------------------------------------------------
    def field_list(self):
        return ['request_id', 'status', 'updated', 'ep_src1', 'ep_src2',
                'ep_dst', 'is_gc', 'openid', 'email_addr', 'xfr1', 'xfr2']
                
    # -----------------------------------------------------------------------
    def field_normalize(self, adict):
        rval = {}
        fl = self.field_list()

        if 'updated' in adict.keys():
            raise RQDBError("User not allowed to set field 'updated'")
        
        for fld in adict.keys():
            if fld in fl:
                if fld == 'is_gc':
                    # adict['is_gc'] is True or False
                    # the database value is 1 or 0
                    if adict[fld]:
                        rval[fld] = 1
                    else:
                        rval[fld] = 0
                else:
                    rval[fld] = adict[fld]
            else:
                raise RQDBError("unknown field '%s'" % fld)
        
        for fld in self.field_list():
            if fld not in adict.keys():
                if fld == 'is_gc':
                    rval[fld] = 0
                else:
                    rval[fld] = ''
            
    # -----------------------------------------------------------------------
#     def insert(rqid = None, status = 'start', ep_src1 = '', ep_src2 = '',
#                ep_dst = '', is_gc = False, openid = '', email_addr = '',
#                xfr1 = '', xfr2 = ''):
    def insert(self, **adict):
        """
        This routine will insert a new record into the database. The
        user can't specify the request_id or updated but all other
        fields may be specified. The default values will be '' for
        text fields and 0 for numeric fields (is_gc is the only one
        the user can specify).
        """

        rval = None
        if 'request_id' in adict.keys():
            raise RQDBError("User can't set request_id on insert")
        adict['request_id'] = uuid.uuid4().hex
        parms = self.field_normalize(adict)

        rval = parms['request_id']

        parmlist = []
        for fld in self.field_list():
            parmlist.append(parms[fld])

        c = self.cxn.cursor()
        c.execute("""insert into request_table (
                        request_id, status, updated,
                        ep_src1, ep_src2, ep_dst, is_gc,
                        openid, email_addr, xfr1, xfr2
                     ) values (
                        '?', '?', ?,
                        '?', '?', '?', ?,
                        '?', '?', '?', '?'
                     )""", parmlist)

        return rval
    
    # -----------------------------------------------------------------------
#     def update(request_id = None, status = None, ep_src1 = None, ep_src2 = None,
#                 ep_dst = None, is_gc = None, openid = None, email_addr = None,
#                 xfr1 = None, xfr2 = None):

    def update(self, **adict):
    
         if request_id == None:
             return None
         rval = request_id
         
         cmd = "update request_table set updated = ?"
         arglist = ( time.time(), )

         for k in kwargs.keys():
             if k == 'is_gc':
                 cmd += ", %s = '?'" % k
             else:
                 cmd += ", %s = '?'" % k
             arglist.__add__(( kwargs[k], ))
             
#          if status != None:
#              cmd += ", status = '?'"
#              arglist.__add__((status,))
#          if ep_src1 != None:
#              cmd += ", ep_src1 = '?'"
#              arglist.__add__((ep_src1,))
#          if ep_src2 != None:
#              cmd += ", ep_src2 = '?'"
#              arglist.__add__((ep_src2,))
#          if ep_dst != None:
#              cmd += ", ep_dst = '?'"
#              arglist.__add__((ep_dst,))
#          if is_gc != None:
#              cmd += ", is_gc = '?'"
#          if openid != None:
#              cmd += ", openid = '?'"
#          if email_addr != None:
#              cmd += ", email_addr = '?'"
#          if xfr1 != None:
#              cmd += ", xfr1 = '?'"
#          if xfr2 != None:
#              cmd += ", xfr2 = '?'"

         cmd += " where request_id = '?';"

         c = self.cxn.cursor()
         c.execute(cmd, arglist)

# ---------------------------------------------------------------------------
class RQDBError(Exception):
    pass
    
# ---------------------------------------------------------------------------
class RQDBTest(unittest.TestCase):
    def test_insert_invalid_field_fail(self):
        pdb.set_trace()
        db = rqdb()
        reqid = uuid.uuid4()
        self.assertRaises(RQDBError, db.insert, rqid = reqid.hex,
                          status = 'start',
                          openid = my_openid,
                          email_addr = 'tbarron@ornl.gov')

    def test_insert_updated_fail(self):
        db = rqdb()
        self.assertRaises(RQDBError, db.insert,
                          status = 'start',
                          updated = time.time(),
                          openid = my_openid,
                          email_addr = 'tbarron@ornl.gov')

    def test_insert_reqid_fail(self):
        db = rqdb()
        self.assertRaises(RQDBError, db.insert,
                          status = 'start',
                          request_id = uuid.uuid4(),
                          openid = my_openid,
                          email_addr = 'tbarron@ornl.gov')

    def test_insert_legal_ok(self):
        db = rqdb()
        rc = db.insert(status = 'start',
                       open_id = my_openid,
                       email_addr = 'tbarron@ornl.gov')
        self.assertEqual(len(rc), 32,
                         msg="uuid hex value (%s) should be 32 long but is %d"
                         % (rc, len(rc)))
        x = uuid.UUID(rc)
        self.assertEqual(len(x.fields), 6,
                         msg="rc is not a valid uuid")

    def test_update_norqid_fail(self):
        db = rqdb()
        self.assertRaises(RQDBError, db.update,
                          status='transfer',
                          ep_src1 = 'somewhere')

    def test_update_badrqid_fail(self):
        db = rqdb()
        rqid = uuid.uuid4()
        self.assertRaises(RQDBError, db.update,
                          request_id = rqid.hex,
                          status='transfer',
                          ep_src1 = 'somewhere')

    def test_update_badrqid_fail(self):
        db = rqdb()
        rqid = db.insert(status='start',
                         openid=my_openid,
                         email_addr = 'tbarron@ornl.gov')
        db.update(request_id = rqid,
                  status = 'transfer',
                  ep_src1 = 'somewhere else')
        row = db.retrieve(request_id = rqid)
        self.assertEqual(row['request_id'], rqid)
        self.assertEqual(row['status'], 'transfer')
        # self.assertEqual(row['updated'], 'transfer')
        self.assertEqual(row['ep_src1'], 'somewhere else')
        self.assertEqual(row['ep_src2'], '')
        self.assertEqual(row['ep_dst'], '')
        self.assertEqual(row['is_gc'], 0)
        self.assertEqual(row['openid'], my_openid)
        self.assertEqual(row['email_addr'], my_address)
        self.assertEqual(row['xfr1'], '')
        self.assertEqual(row['xfr2'], '')
        
if __name__ == '__main__':
    unittest.main()
